#!/usr/bin/perl -w
#
# MyBACMan.pl
#
# STARTED: 10/1/03
# LAST UPDATED: 10/1/03
# AUTHOR: Jamie Estill, UGA Plant Genome Mapping Lab,
# EMAIL: jestill@uga.edu
#
# Creates a BACMan database in MySQL that is suitable for use as a backend
# to the BACMan frontend, and as a datasource for publishing data on the web.


# LOAD REQUIRED LIBRARIES
use DBI;                             # The module for interacting with the database
use Getopt::Std;                     # The moduce for reading options from the input string

my %Options;
getopts('d:h:u:p:o', \%Option);      # Load the options that are sent into the hash named options
                                     # $Options{d] is the database for the DBI connection
                                     # $Options{h} is the database host name for the DBI connection
                                     # $Options{u} is the username for the DBI connection
                                     # $Options{p} is the password for the DBI connection
                                     # $Options{a} is the append value the table construction
# GET START TIME
$TimeStartAll = time;

# SET VARIABLES
$database = $Option{d};
$host = $Option{h};
$user = $Option{u};
$password = $Option{p};
#print "Database is ".$database," \n";
#print "Host is ".$host." \n";
#print "User is ".$user." \n";
#print "Password is ".$password." \n";

#$DatabaseParentDir = '/home/jestill/BACMan';   # Set the database parent directory to the BACMan directory in my home
$DatabaseDir = '/home/jestill/BACMan';
($RealMonth, $Day, $Year, $Hour, $Minute, $Second, $ampm) = GetTime(0,1,2,3,4,5,6);

# CHECK FOR VALID VALUES FOR THE APPEND BOOLEAN
if ($Option{o}) {
    print "\n \nAre you sure you want to delete the existing data (y/n). ";
    my $answer = <STDIN>;
    print "The input was ".$answer." \n";
    if ($answer=~"y")
    {
        print "Overwrite is true. Existing data will be deleted. \n";
        $Overwrite = "true";
    }
    #else
    elsif ($answer=~"n")
    {
     	print "Overwrite is false. \nExisting data will not be deleted, new records will be appened to the data table. \n";
    	$Overwrite = "false";
    }
    else
    {
    	print "ERROR: Valid values are y and n. \n";
    	exit;
    }
} else {
    print "Overwrite is false. Data will be appended to the existing table. \n";
    $Overwrite = "false";
    #exit;
}



# Move to the directory that contains the data
chdir "$DatabaseDir";

##################################################
# MAKE THE TABLES IS NEEDED AND FILL THEM        #
# WITH DATA                                      #
##################################################

# The LoadTable Subfucntion call is &LoadTable("TableName", $MakeTableString, "Table")
#Test the make table function and database connection

# tblListLibrary
print "Loading data for the table tblListLibrary \n";
$MakeListLibrary = " CREATE TABLE tblListLibrary
(
  Library text ,
  Taxon text ,
  Coverage int(11) ,
  NumberBACs int(11) ,
  InsertSize int(11) ,
  Color text ,
  GriddingFormat text ,
  FPCPrefix text
) ;";
&LoadTable ("tblListLibrary", $MakeListLibrary, "tblListLibrary.txt");

# tblFPCtoFPPConvert
print "Loading data for tblFPCtoFPPconvert \n";
$MakeFpcFPP = "CREATE TABLE tblFPCtoFPPconvert
(
  ID int(11) ,
  Library text ,
  fpcnumber text ,
  fppnumber text ,
  contig1 text ,
  contig2 text
) ;";
&LoadTable ("tblFPCtoFPPconvert", $MakeFpcFPP, "tblFPCtoFPPconvert.txt");
&IndexTable("tblFPCtoFPPconvert", "Library(10)");                        # index the first 10 characters in the field Library
&IndexTable("tblFPCtoFPPconvert", "fppnumber(10)");                      # index the first 10 characters in the field fppnumber

# tblHitDataFiltered
print "Loading data for the table tblHitDataFiltered \n";
$MakeHitDataFiltered = " CREATE TABLE tblHitDataFiltered
(
  Probe text ,
  Full text ,
  Quality tinyint(4),
  Library text ,
  Experiment text ,
  PossibleProbes smallint(6) ,
  FPCContig text ,
  TheKey int(11)
) ;";
&LoadTable ("tblHitDataFiltered", $MakeHitDataFiltered, "tblHitDataFiltered.txt");
&IndexTable("tblHitDataFiltered", "Probe(10)");                                   # index the first 10 characters in the field Probe
&IndexTable("tblHitDataFiltered", "Full(10)");                                    # index the first 10 characters in the field Full
&IndexTable("tblHitDataFiltered", "Library(10)");                                 # index the first 10 characters in the field Library

# tblHybData
print "Loading data for the table tblHybData \n";
$MakeHybData = " CREATE TABLE tblHybData
(
  TheKey int(11) ,
  BID text ,
  filt text ,
  Probe text ,
  FilterType text ,
  Date datetime ,
  Person text ,
  N text ,
  Library text ,
  FNum tinyint(4) ,
  HybType text ,
  Scored tinyint(4) ,
  ECStatus text ,
  ErrorChecked tinyint(4) ,
  FilmVerified tinyint(4) ,
  Notes mediumtext ,
  Scoreperson text ,
  EditedPerson text ,
  Experiment text ,
  RowsHitDataRaw int(11) ,
  Not_  smallint(6) ,
  Under smallint(6) ,
  Match_ smallint(6) ,
  Over smallint(6) ,
  Exp smallint(6) ,
  PNot float ,
  PUnder float ,
  PMatch float ,
  POver float ,
  PExp float ,
  SumHits smallint(6)
) ;";
&LoadTable ("tblHybData", $MakeHybData, "tblHybData.txt");


# tblListOvergoList
print "Loading data for the table tblListOvergoList \n";
$MakeOvergoTable = "CREATE TABLE tblListOvergoList
(
  ID1 int(11) ,
  OVERGO text ,
  GenbankNumber text ,
  SucestNumber text ,
  ProbeName text ,
  SourceOfSequence text ,
  ReasonForOvergo text,
  OvergoA text,
  OvergoB text,
  FullProbeSeq text,
  SpeciesSource text,
  OvergoSet text,
  ReasonForOvergo2 text
) ;";

&LoadTable ("tblListOvergoList", $MakeOvergoTable, "tblListOvergoList.txt");
&IndexTable("tblListOvergoList", "GenbankNumber(15)");                                 # index the first 10 characters in the field Library
&IndexTable("tblListOvergoList", "ProbeName(15)");                                     # index the first 10 characters in the field Library


# tblProbesByGenbank
print "Loading data for the table tblProbesByGenbank \n";
$Make = "CREATE TABLE tblProbesByGenbank
(
  TheKey int(11) ,
  GenbankNumber text ,
  ProbeName text ,
  SucestNumber text ,
  OVERGO text
) ;";
&LoadTable ("tblProbesByGenbank", $Make, "tblProbesByGenbank.txt");
&IndexTable("tblProbesByGenbank", "GenbankNumber(15)");                 # add index to the first 10 characters of the ProbeName


# tblProbesByName
print "Loading data for the table tblProbesByName \n";
$Make = "CREATE TABLE tblProbesByName
(
  TheKey int(11) ,
  ProbeName text,
  GenbankNumber text ,
  SucestNumber text ,
  OVERGO text
) ;";
&LoadTable ("tblProbesByName", $Make, "tblProbesByName.txt");
&IndexTable("tblProbesByName", "ProbeName(15)");                 # add index to the first 10 characters of the ProbeName


# tblProbesByPgmlCode
print "Loading data for the table tblProbesByPgmlCode \n";
$Make = "CREATE TABLE tblProbesByPgmlCode
(
  TheKey int(11) ,
  OVERGO text ,
  ProbeName text ,
  GenbankNumber text ,
  SucestNumber text
) ;";
&LoadTable ("tblProbesByPgmlCode", $Make, "tblProbesByPgmlCode.txt");
&IndexTable("tblProbesByPgmlCode", "OVERGO(15)");                 # add index to the first 10 characters of the ProbeName




$TimeEndAll = time;
$TotalTime = $TimeEndAll - $TimeStartAll;
print "The total time to build the load the BACMan database for ".$database." was ".$TotalTime." seconds \n";

exit;

##################################################
# SUBROUTINE TO CREATE A TABLE AND LOAD DATA     #
# FROM A PROPERLY DELINEATED TEXT FILE           #
##################################################

sub LoadTable
{

$TableName = $_[0];                               # The table name that will be created
$CreateTable = $_[1];                             # The make table query
$DataFile = $_[2];                                # The file that contains the data

# Connect to the database using the variables passed to the program
my $dbh = DBI->connect("DBI:mysql:database=".$database.";"."host=".$host,
                           $user, $password,
                         {'RaiseError' => 1});

# DROP THE TABLE if it already exists and the user has selected to delete existing tables
if (&does_table_exist($dbh,$TableName))   # if the table already exists then check to see if the user wants to overwrite the existing table
{

    if ($Overwrite =~ "true")
    {
    print "The value for overwrite was true \n";
    print "The table ".$TableName." already exists and must be deleted ..... ";
    $dbh->do("DROP TABLE ".$TableName);
    print "completed.", "\n";

    # CREATE A NEW 'EMPTY' VERSION OF THE TABLE
    print "Creating a new copy of the table".$TableName." \n";
    $dbh->do($CreateTable);
    }
}
else                                      # if the table does not exist then make a new copy of the table
{
    # CREATE A NEW 'EMPTY' VERSION OF THE TABLE
    print "The table ".$TableName." does not exist. Creating the table. \n";
    $dbh->do($CreateTable);
}



#$PopulateTable = "LOAD DATA LOCAL INFILE
#             '".$DatabaseDir."/".$DataFile."'
#             INTO TABLE ".$TableName."
#             FIELDS TERMINATED BY '|'
#             LINES TERMINATED BY '\n'";

# added the enclosed by
$PopulateTable = "LOAD DATA LOCAL INFILE
             '".$DatabaseDir."/".$DataFile."'
             INTO TABLE ".$TableName."
             FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
             LINES TERMINATED BY '\n'";

print "Populating the table ".$TableName." using data from the file ".$DataFile." \n";

print "Loading the data \n";
$dbh->do($PopulateTable);
$dbh->disconnect();               # Drop the connection to the database

}


##################################################
# SUBROUTINE TO CREATE A TABLE AND LOAD DATA     #
# FROM A PROPERLY DELINEATED TEXT FILE           #
##################################################

sub IndexTable
{

$TableName = $_[0];                               # The table name that will be created
$IndexField = $_[1];                              # The field to make the index on

# Connect to the database using the variables passed to the program
my $dbh = DBI->connect("DBI:mysql:database=".$database.";"."host=".$host,
                           $user, $password,
                         {'RaiseError' => 1});

print "Creating index on the table ".$TableName." for the field ".$IndexField." \n";
$AddIndex = "ALTER TABLE ".$TableName." ADD INDEX ( ".$IndexField." ) ;";
$dbh->do($AddIndex);
$dbh->disconnect();               # Drop the connection to the database

}


##################################################
# SUBROUTINE TO GET THE CURRENT TIME IN A        #
# USABLE FORM                                    #
##################################################

sub GetTime
  {
   ($Second, $Minute, $Hour, $Day, $Month, $Year, $WeekDay, $DayOfYear, $IsDST) = localtime(time);

   my $RealMonth = $Month + 1;

   if($RealMonth < 10)
     {
      $RealMonth = "0" . $RealMonth; # add a leading zero to one-digit months
    }

   if($Day < 10)
     {
      $Day = "0" . $Day; # add a leading zero to one-digit days
    }

   if ($Hour < 12) {$ampm = "AM"}
   else {$Hour = $Hour - 12; $ampm = "PM"}

   if ($Hour == 0) {$Hour = $Hour + 12}

   $Year = $Year + 1900;

   return $RealMonth, $Day, $Year, $Hour, $Minute, $Second, $ampm

}

##################################################
# SUBROUTINE TO CHECK TO SEE IF THE              #
# MYSQL TABLE ALREADY EXISTS                     #
##################################################
sub does_table_exist
{
                my ($dbh,$whichtable) = @_;
                my ($table,@alltables,$found);
                @alltables = $dbh->tables();
                $found = 0;
                foreach $table (@alltables) {
                        $found=1 if ($table eq $whichtable)
                        # $found=1 if ($table eq $test_tabelle);
		      }
                # return true if table was found
                # return false if not found
                return $found;
}

##################################################
# SUBROUTINE TO SEE HOW MANY RECORDS             #
# EXIST IN THE MYSQL TABLE                       #
##################################################
sub how_many_records
{
                my ($dbh,$whichtable) = @_;
                my ($result,$cur,$sql,@row);

                $sql = "select count(*) from $whichtable";
                $cur = $dbh->prepare($sql);
                $cur->execute();
                @row=$cur->fetchrow;
                $result=$row[0];
                $cur->finish;
                return $result;
}

